package com.blog.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.blog.BO.DiscussBO;
import com.blog.dao.BaseDao;
import com.blog.dao.DiscussDao;
import com.blog.entity.Discuss;

public class DiscussDaoImpl implements DiscussDao {

	@Override
	public List<Discuss> selectDiscussByDiscussBO(DiscussBO discussBO, int pageNum, int pageSize) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		List<Discuss> discussList = new ArrayList<Discuss>();
		List<Object>  params = new ArrayList<Object>();
		StringBuffer sql = new StringBuffer("SELECT * FROM discuss where 1 = 1");
		if(discussBO != null) {
			//查询条件
			if(discussBO.getDiscussId() != 0) {
				sql.append(" AND discussId = ?");
				params.add(discussBO.getDiscussId());
			}//评论id
			if(discussBO.getDiscussText()!=null && !"".equals(discussBO.getDiscussText())) {
				sql.append(" AND discussText LIKE concat('%',?,'%')");
				params.add(discussBO.getDiscussText());
			}//评论内容
			if(discussBO.getSendId() != 0) {
				sql.append(" AND sendId = ?");
				params.add(discussBO.getSendId());
			}//评论发送方的用户id
			if(discussBO.getReceiveId() != 0) {
				sql.append(" AND receiveId = ?");
				params.add(discussBO.getReceiveId());
			}//评论回复给的用户id
			if(discussBO.getPostId() != 0) {
				sql.append(" AND postId = ?");
				params.add(discussBO.getPostId());
			}//评论所属板块id
			if(discussBO.getSendDate()!=null) {
				sql.append(" AND sendDate = ?");
				params.add(discussBO.getSendDate());
			}//帖子创建时间
			if(discussBO.getSendStartDate()!=null) {
				sql.append(" AND sendDate >= ?");
				params.add(discussBO.getSendStartDate());
			}//评论从什么时间开始，包含输入的时间
			if(discussBO.getSendEndDate()!=null) {
				sql.append(" AND sendDate <= ?");
				params.add(discussBO.getSendEndDate());
			}//评论从什么时候结束，包含输入的时间
		}
		sql.append(" LIMIT ?,?");
		Discuss selectDiscuss = null;
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql.toString());
			for(int i =0;i<params.size();i++) {
				ps.setObject(i+1, params.get(i));
			}
			ps.setInt(params.size()+1, (pageNum-1)*pageSize);
			ps.setInt(params.size()+2, pageSize);
			res = ps.executeQuery();
			while(res.next()) {
				selectDiscuss = new Discuss();
				selectDiscuss.setDiscussId(res.getInt("discussId"));
				selectDiscuss.setDiscussText(res.getString("discussText"));
				selectDiscuss.setSendId(res.getInt("sendId"));
				selectDiscuss.setReceiveId(res.getInt("receiveId"));
				selectDiscuss.setPostId(res.getInt("postId"));
				SimpleDateFormat format=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				Date dt = null;
				try {
					dt = format.parse(format.format(res.getTimestamp("sendDate")));
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					System.out.println("转换成Date的String不是规范值");
					e.printStackTrace();
				}
				selectDiscuss.setSendDate(dt);
				discussList.add(selectDiscuss);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			BaseDao.closeAll(res, ps, con);
		}
		return discussList;
	}

	@Override
	public int insertDiscuss(Discuss discuss) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement ps = null;
		String sql = "INSERT INTO discuss(discussText,sendId,receiveId,postId,sendDate) VALUES (?,?,?,?,?);";
		int resultNum = 0;
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setString(1,discuss.getDiscussText());
			ps.setInt(2,discuss.getSendId());
			ps.setInt(3,discuss.getReceiveId());
			ps.setInt(4,discuss.getPostId());
			SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			String dt = format.format(discuss.getSendDate().getTime());
			ps.setString(5,dt);
			resultNum = ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			BaseDao.closeAll(null, ps, con);
		}
		return resultNum;
	}

	@Override
	public int deleteDiscussByDiscussBO(DiscussBO discussBO) {
		// TODO Auto-generated method stub
		List<Object>  params = new ArrayList<Object>();
		int resultNum = 0;
		Connection con = null;
		PreparedStatement ps = null;
		StringBuffer sql = new StringBuffer("DELETE FROM discuss WHERE 1 = 1");
		if(discussBO.getDiscussId() != 0) {
			sql.append(" AND discussId = ?");
			params.add(discussBO.getDiscussId());
		}//评论id
		if(discussBO.getDiscussText()!=null && !"".equals(discussBO.getDiscussText())) {
			sql.append(" AND discussText = ?");
			params.add(discussBO.getDiscussText());
		}//评论内容
		if(discussBO.getSendId() != 0) {
			sql.append(" AND sendId = ?");
			params.add(discussBO.getSendId());
		}//评论所属用户id
		if(discussBO.getReceiveId() != 0) {
			sql.append(" AND receiveId = ?");
			params.add(discussBO.getReceiveId());
		}//评论接收用户id
		if(discussBO.getPostId() != 0) {
			sql.append(" AND postId = ?");
			params.add(discussBO.getPostId());
		}//评论所属帖子id
		if(discussBO.getSendDate()!=null) {
			sql.append(" AND sendDate = ?");
		}//评论创建时间
		if(discussBO.getSendStartDate()!=null) {
			sql.append(" AND sendDate >= ?");
		}//评论从什么时间开始，包含输入的时间
		if(discussBO.getSendEndDate()!=null) {
			sql.append(" AND sendDate <= ?");
		}//评论从什么时候结束，包含输入的时间
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql.toString());
			for(int i =0;i<params.size();i++) {
				ps.setObject(i+1, params.get(i));
			}
			SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			String dt1 = format.format(discussBO.getSendDate().getTime());
			ps.setString(params.size()+1,dt1);
			String dt2 = format.format(discussBO.getSendStartDate().getTime());
			ps.setString(params.size()+2,dt2);
			String dt3 = format.format(discussBO.getSendEndDate().getTime());
			ps.setString(params.size()+3,dt3);
			resultNum = ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			BaseDao.closeAll(null, ps, con);
		}
		return resultNum;
	}

	@Override
	public int updateDiscuss(Discuss discuss) {
		// TODO Auto-generated method stub
		List<Object>  params = new ArrayList<Object>();
		int resultNum = 0;
		if(discuss.getPostId() != 0) {
			Connection con = null;
			PreparedStatement ps = null;
			StringBuffer sql = new StringBuffer("UPDATE allpost SET discussId = ?");
			params.add(discuss.getDiscussId());
			if(discuss.getDiscussText()!=null && !"".equals(discuss.getDiscussText())) {
				sql.append(",discussText = ?");
				params.add(discuss.getDiscussText());
			}//评论内容
			if(discuss.getSendId() != 0) {
				sql.append(",sendId = ?");
				params.add(discuss.getSendId());
			}//评论发送方用户id
			if(discuss.getReceiveId() != 0) {
				sql.append(",receiveId = ?");
				params.add(discuss.getReceiveId());
			}//评论接收方用户id
			if(discuss.getPostId() != 0) {
				sql.append(",postId = ?");
				params.add(discuss.getPostId());
			}//评论所属板块id
			sql.append(",sendDate = ?");
			sql.append(" WHERE discussId = ?");
			try {
				con = BaseDao.getCon();
				ps = con.prepareStatement(sql.toString());
				for(int i =0;i<params.size();i++) {
					ps.setObject(i+1, params.get(i));
				}
				SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				String dt = format.format(new Date().getTime());
				ps.setString(params.size()+1,dt);
				ps.setInt(params.size()+2,discuss.getDiscussId());
				resultNum = ps.executeUpdate();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {
				BaseDao.closeAll(null, ps, con);
			}
		}
		return resultNum;
	}

	@Override
	public int selectNum(int postId) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		String sql = "SELECT COUNT(1) AS discussNum FROM Discuss WHERE postId =?;";
		int discussNum = 0;
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setInt(1, postId);
			res = ps.executeQuery();
			if(res.next()) {
				discussNum = res.getInt("discussNum");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			BaseDao.closeAll(res, ps, con);
		}
		return discussNum;
	}

}
